﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using ThapCam.System;

namespace ThapCam.Ulti
{
    public enum SqlPool
    {
        ReadPool=1,
        WritePool=2
    }

    public sealed class SqlHelper
    {
        private SqlPool _pool;
        private MySqlConnection _mysqlConnection;
        private MySqlCommand _mysqlCommand;
        private MySqlDataAdapter _mysqlDadapter;
        private MySqlDataReader _mysqlDataReader;

        public SqlHelper()
        {
            _pool = SqlPool.ReadPool;
            _mysqlConnection = new MySqlConnection(getConnectionString());
        }

        public SqlHelper(SqlPool pool)
        {
            _pool = pool;
            _mysqlConnection = new MySqlConnection(getConnectionString());
        }

        private String getConnectionString()
        {
            if (_pool == SqlPool.ReadPool)
                return WebConfig.READ_POOL;
            else if (_pool == SqlPool.WritePool)
                return WebConfig.WRITE_POOL;            
            return null;
        }

        public DataTable executeQuery(string statement, CommandType type, params SqlParameter[] parameter)
        {
            DataTable table = null;
            try
            {
                _mysqlCommand = new MySqlCommand(statement, _mysqlConnection);
                _mysqlCommand.CommandType = type;
                addParameter(parameter);
                _mysqlDadapter = new MySqlDataAdapter(_mysqlCommand);
                table = new DataTable();
                _mysqlDadapter.Fill(table);
            }
            catch (MySqlException ex)
            {
                LogManager.writeError(this.GetType(), null, ex);
            }
            finally
            {
                destroy();
            }
            return table;
        }

        public int executeNonQuery(string procName, CommandType type, params SqlParameter[] parameter)
        {
            try
            {
                _mysqlCommand = new MySqlCommand(procName, _mysqlConnection);
                _mysqlCommand.CommandType = type;
                addParameter(parameter);
                _mysqlConnection.Open();
                return _mysqlCommand.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                LogManager.writeError(this.GetType(), null, ex);
            }
            finally
            {
                destroy();
            }
            return 0;
        }

        public Dictionary<string, object> executeScalar(string procName, CommandType type, params SqlParameter[] parameter)
        {
            Dictionary<string, object> data = null;
            try
            {
                _mysqlCommand = new MySqlCommand(procName, _mysqlConnection);
                _mysqlCommand.CommandType = type;
                addParameter(parameter);
                _mysqlConnection.Open();
                _mysqlDataReader = _mysqlCommand.ExecuteReader();
                if (_mysqlDataReader.Read())
                {
                    data = new Dictionary<string, object>();
                    int filedCount = _mysqlDataReader.FieldCount;
                    for (var index = 0; index < filedCount; index++)
                    {
                        data.Add(_mysqlDataReader.GetName(index), _mysqlDataReader.GetValue(index));
                    }
                }
                _mysqlDataReader.Close();
            }
            catch (MySqlException ex)
            {
                LogManager.writeError(this.GetType(), null, ex);
            }
            finally
            {
                destroy();
            }
            return data;
        }

        
        private void addParameter(params SqlParameter[] parameter)
        {
            if (_mysqlCommand != null)
            {
                foreach (SqlParameter param in parameter)
                {
                    _mysqlCommand.Parameters.Add(param.getParameter);
                }
            }
        }

        public void destroy()
        {
            if (_mysqlConnection != null)
            {
                if (_mysqlConnection.State == ConnectionState.Open)
                {
                    _mysqlConnection.Close();
                    _mysqlConnection.Dispose();
                }
            }
            if (_mysqlDataReader != null)
            {
                if (!_mysqlDataReader.IsClosed)
                    _mysqlDataReader.Close();
                _mysqlDataReader.Dispose();
            }
            if (_mysqlCommand != null)
                _mysqlCommand.Dispose();
            if (_mysqlDadapter != null)
                _mysqlDadapter.Dispose();
            _mysqlConnection = null;
            _mysqlCommand = null;
            _mysqlDadapter = null;
            _mysqlDataReader = null;
        }

       
    }

    /*
     * Class SqlParameter
     */
    public sealed class SqlParameter
    {
        private MySqlParameter _param;

        public SqlParameter()
        {
        }

        public SqlParameter(string paramName, object value, MySqlDbType dataType, bool isOutPut = false)
        {
            _param = new MySqlParameter(paramName, dataType);
            _param.Value = value;
            if (isOutPut)
                _param.Direction = ParameterDirection.Output;
            else
                _param.Direction = ParameterDirection.Input;
        }

        public MySqlParameter getParameter
        {
            get
            {
                return _param;
            }
        }

        public void addParam(string paramName, object value, MySqlDbType dataType, bool isOutPut = false)
        {
            _param = new MySqlParameter(paramName, dataType);
            _param.Value = value;
            if (isOutPut)
                _param.Direction = ParameterDirection.Output;
            else
                _param.Direction = ParameterDirection.Input;
        }

        public object getValue()
        {
            return _param == null ? null : _param.Value;
        }
    }
}
